#!/usr/bin/python
#-*-coding: utf-8 -*-

"""This module connects the Aangel and the SQL Database.

It very similar with the Deemon's module connectSQL, but the methods are
diferent and, since in a computer with Aangel the Deemon may be not installed,
we chose to not unify the modules.

Like Deemon SQL connection, in version 3.1, the SQL Database is a MySQL server 
and uses the MySQLdb module.

It's possible extend it to others SQL Databases, like PostgreeSQL or SQL Server.

"""

import MySQLdb
import os

from time import localtime, strftime

class Connection(object):
    """The SQL Database interface.
    
    This Class has methods to get data of jobs to give a cluster ID,
    get the clusters with free resources, and send the results back to database.
    
    In the future, will able to connect using several sql engines. 
    
    """
    
    def __init__(self, params):
        
        try:
            conn = MySQLdb.connect(params['IP'], params['DBUser'], 
                                   params['Passwd'], params['DataBase'])
            self.cur = conn.cursor()
        except MySQLdb.OperationalError, erro:
            print erro[1]
            # raise ErroSQL('OPERATIONAL')
        else:
            print 'Connected to the SQL server'
            
    def getJobs(self):
        """Capture jobs can be schedule.
        
        In version 1.0, only verify the jobs with cluster id = 0.
        Later, will reschedule jobs that have not were captured by its cluster.
        
        """
        
        query = """SELECT jobId, projectId FROM jobs j WHERE j.clusterId = 0"""
        self.cur.execute(query)
        
        # cria um dicionario com itens iguais a (jobId: projectId)
        j = dict(self.cur)
        
        if j:
            print 'Jobs Selecionados'
        else:
            return None
        
        # Agora, pega o numero de processadores necessarios para cada job
        # a partir do Projeto ao qual o Job pertence.
        jobs = {}
        
        # ainda esta ineficiente, pois pesquisa o mesmo projeto varias vezes.   
        for jobId in j:
            
            projectId = j[jobId]       
            query = """SELECT projectRequiredProcessors FROM projects p \
                    WHERE p.projectId = %s""" 
            self.cur.execute(query, (projectId,))
            processors = self.cur.fetchone()[0]
            jobs[jobId] = processors
         
        # Jobs eh um dicionario com itens iguais a (jobId: RequiredProcessors)
        l = jobs.items()
        return l

    def getClusters(self):
        """ Capture clusters that can get jobs. 
        
         To do so, takes into account the table field that 
         displays the free system resources.
         
         """
        
        query = """SELECT clusterId, clusterWorkLoad FROM clusters c \
                WHERE c.clusterWorkLoad != 0 AND c.clusterActive = 1"""
        n = self.cur.execute(query)
        
        if n:
            # se ha recursos disponiveis
            # print 'Recursos disponiveis.'
            
            # cria um dict com os clusters que tem recursos disponiveis
            # armazenando o id e a quantidade de recursos
            return list(self.cur)
        
        else:
            return None 
        
    def sendResults(self, jobList):
        """Send the list of jobs with the clusterId's to the database."""
        
        print 'Enviando resultados ao Banco de Dados...'
        
        query = """UPDATE jobs SET clusterId = (%s) WHERE jobId = (%s)"""
        
        for job in jobList:
            self.cur.execute(query, (job[1], job[0]))
            
    def closeConn(self):
        """Close the connection with de SQL Database. """
        
        self.cur.close()
